﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using VK.Comm.TransactSql;

namespace VK.Casemeet.Pages
{
    internal static class CustomPageDAL
    {
        internal static CustomPageEntity GetCustomPageByReader(IDataReader dr)
        {
            CustomPageEntity customPage = null;

            customPage = new CustomPageEntity();
            customPage.PageID = dr.GetInt32(0);
            customPage.Type = dr.GetInt32(1);
            customPage.Title = dr.IsDBNull(2) ? null : dr.GetString(2);
            customPage.Content = dr.IsDBNull(3) ? null : dr.GetString(3);
            customPage.IsEnable = dr.GetBoolean(4);

            return customPage;
        }

        internal static int GetCustomPageCount(IList<Condition> conditionArray)
        {
            Database db = DatabaseFactory.CreateDatabase();
            int result = 0;

            string SelectCustomPage = @"SELECT COUNT([PageID]) FROM [CustomPage]";

            List<DbParameter> parameterlst = new List<DbParameter>();
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectCustomPage, ref parameterlst);
            DbCommand SelectCustomPageCommand = db.GetSqlStringCommand(SelectCustomPage);
            DbHelper.PackDbCommandByDbParameters(SelectCustomPageCommand, parameterlst.ToArray());

            object obj = db.ExecuteScalar(SelectCustomPageCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }

        internal static List<CustomPageEntity> GetCustomPageList(int startRecordIndex, int endRecordIndex, IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<CustomPageEntity> customPagelst = new List<CustomPageEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectCustomPage = @"
                SELECT [PageID],[Type],[Title],[Content],[IsEnable]
	            FROM (
                    SELECT [PageID],[Type],[Title],[Content],[IsEnable],ROW_NUMBER() OVER (ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectCustomPage += " " + sortField + " " + sortMode + ",";
            SelectCustomPage += @" [PageID] Desc
                ) AS rowNum 
                    FROM [CustomPage] ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectCustomPage, ref parameterlst);
            SelectCustomPage += @"
                ) AS [CustomPage] 
	            WHERE rowNum BETWEEN @StartRecordIndex and @EndRecordIndex ";

            DbCommand SelectCustomPageCommand = db.GetSqlStringCommand(SelectCustomPage);
            parameterlst.Add(DbHelper.PackInParameter(db, "@StartRecordIndex", DbType.Int32, startRecordIndex));
            parameterlst.Add(DbHelper.PackInParameter(db, "@EndRecordIndex", DbType.Int32, endRecordIndex));
            DbHelper.PackDbCommandByDbParameters(SelectCustomPageCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectCustomPageCommand))
            {
                while (dr.Read())
                {
                    CustomPageEntity customPage = GetCustomPageByReader(dr);

                    customPagelst.Add(customPage);
                }
            }

            return customPagelst;
        }

        internal static CustomPageEntity GetCustomPage(int customPageID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            CustomPageEntity customPage = null;

            string SelectCustomPage = @"
                SELECT [PageID],[Type],[Title],[Content],[IsEnable]
                FROM [CustomPage] WHERE PageID=@PageID
	           ";

            DbCommand SelectCustomPageCommand = db.GetSqlStringCommand(SelectCustomPage);
            db.AddInParameter(SelectCustomPageCommand, "@PageID", DbType.Int32, customPageID);

            using (IDataReader dr = db.ExecuteReader(SelectCustomPageCommand))
            {
                if (dr.Read())
                {
                    customPage = GetCustomPageByReader(dr);
                }
            }

            return customPage;
        }

        internal static CustomPageEntity GetCustomPageByNO(string NO)
        {
            Database db = DatabaseFactory.CreateDatabase();
            CustomPageEntity customPage = null;

            string SelectCustomPage = @"
                SELECT [PageID],[Type],[Title],[Content],[IsEnable]
                FROM [CustomPage] WHERE NO=@NO AND IsEnable=1
	           ";

            DbCommand SelectCustomPageCommand = db.GetSqlStringCommand(SelectCustomPage);
            db.AddInParameter(SelectCustomPageCommand, "@NO", DbType.String, NO);

            using (IDataReader dr = db.ExecuteReader(SelectCustomPageCommand))
            {
                if (dr.Read())
                {
                    customPage = GetCustomPageByReader(dr);
                }
            }

            return customPage;
        }

        internal static int InsertCustomPage(CustomPageEntity customPage)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string InsertCustomPage = @"
            INSERT INTO [CustomPage]
            ([Type],[Title],[Content],[IsEnable])
             VALUES
            (@Type,@Title,@Content,@IsEnable)";

            DbCommand InsertCustomPageCommand = db.GetSqlStringCommand(InsertCustomPage);
            db.AddInParameter(InsertCustomPageCommand, "@Type", DbType.Int32, customPage.Type);
            db.AddInParameter(InsertCustomPageCommand, "@Title", DbType.String, customPage.Title);
            db.AddInParameter(InsertCustomPageCommand, "@Content", DbType.String, customPage.Content);
            db.AddInParameter(InsertCustomPageCommand, "@IsEnable", DbType.Boolean, customPage.IsEnable);
            result = db.ExecuteNonQuery(InsertCustomPageCommand);

            return result;
        }

        internal static int UpdateCustomPage(CustomPageEntity customPage)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();
            string UpdateCustomPage = @"
            UPDATE [CustomPage]
            SET [Title]=@Title,[Content]=@Content,[IsEnable]=@IsEnable
             WHERE [PageID] = @PageID";

            DbCommand UpdateCustomPageCommand = db.GetSqlStringCommand(UpdateCustomPage);
            db.AddInParameter(UpdateCustomPageCommand, "@Title", DbType.String, customPage.Title);
            db.AddInParameter(UpdateCustomPageCommand, "@Content", DbType.String, customPage.Content);
            db.AddInParameter(UpdateCustomPageCommand, "@IsEnable", DbType.Boolean, customPage.IsEnable);
            db.AddInParameter(UpdateCustomPageCommand, "@PageID", DbType.Int32, customPage.PageID);
            result = db.ExecuteNonQuery(UpdateCustomPageCommand);

            return result;
        }

        /// <summary>
        /// DeleteCustomPage
        /// </summary>
        /// <param name="productCategoryID">productCategoryID</param>
        /// <returns>0：失败 1：删除成功。2：系统属性不能被删除。</returns>
        internal static int DeleteCustomPage(int customPageID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string DeleteCustomPage = @"
/* 0：失败 1：删除成功。2：系统属性不能被删除。 */
	Declare @ChildCount int, @RESULT AS INT
	SET @RESULT=0;
	
    BEGIN TRANSACTION Delete_CustomPage;

    IF EXISTS(SELECT PageID FROM CustomPage WHERE PageID = @PageID AND Type=2)
    BEGIN
		DELETE FROM [CustomPage] WHERE PageID=@PageID
        SET @RESULT=1 
    END
    ELSE
    BEGIN
        SET @RESULT=2 
    END
    
    IF @@ERROR=0 
        BEGIN
        COMMIT TRANSACTION Delete_CustomPage;
        END
    ELSE
        BEGIN
        ROLLBACK TRANSACTION Delete_CustomPage;
        END
		
	SELECT @RESULT";

            DbCommand DeleteCustomPageCommand = db.GetSqlStringCommand(DeleteCustomPage);
            db.AddInParameter(DeleteCustomPageCommand, "@PageID", DbType.Int32, customPageID);
            object obj = db.ExecuteScalar(DeleteCustomPageCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }

        internal static int SetCustomPageEnable(int customPageID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetCustomPage = @"UPDATE [CustomPage]
                   SET [IsEnable] = ([IsEnable]+1)%2
                 WHERE [PageID] = @PageID";

            DbCommand SetCustomPageCommand = db.GetSqlStringCommand(SetCustomPage);
            db.AddInParameter(SetCustomPageCommand, "@PageID", DbType.Int32, customPageID);
            result = db.ExecuteNonQuery(SetCustomPageCommand);

            return result;
        }
    }
}
